﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Flow_AddPostilFile_V2]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Flow_AddPostilFile_V2];
GO
CREATE PROCEDURE [dbo].[sproc_Flow_AddPostilFile_V2]
    @PostilID int,
    @FileName nvarchar(255),
    @FileSize int,
    @FileAttribute int,
    @FileVisualPath nvarchar(255),
    @FileAuthor nvarchar(255),
    @FileCatlog nvarchar(255),
    @FileAddedDate datetime
AS
BEGIN
SET NOCOUNT ON;

IF ( @@TRANCOUNT = 0 )
BEGIN
    RAISERROR(N'This sproc must be executed within an active transaction.', 16, 1);
    RETURN -1;
END

DECLARE @pid INT;

INSERT INTO dbo.uds_Flow_Files (DOCID, [FILENAME], FILESIZE, FILEATTRIBUTE, FILEVISUALPATH, FILEAUTHOR, FILECATLOG, FILEADDEDDATE)
    VALUES(0, @FileName, @FileSize, @FileAttribute, @FileVisualPath, @FileAuthor, @FileCatlog, @FileAddedDate);
SELECT @pid = @@IDENTITY;

-- Update the file id for the postil
UPDATE dbo.uds_flow_postil SET [file_id] = @pid WHERE Postil_ID = @PostilID;
IF (@@ROWCOUNT <> 1)
BEGIN
    RAISERROR(N'Failed to create a new file for the postil.', 16, 1);
    RETURN -1;
END

RETURN @pid;

END
GO
